package cn.lili.modules.order.order.mapper;


import cn.lili.modules.order.order.entity.dos.PrizeOrder;
import cn.lili.modules.order.order.entity.dto.PrizeOrderDTO;
import cn.lili.modules.order.order.entity.vo.PrizeOrderStatisticsVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

/**
 * @author asus
 * @description 针对表【li_prize_order(中奖记录表)】的数据库操作Mapper
 * @createDate 2024-01-18 13:45:59
 * @Entity cn.lili.modules.order.order.entity.dos.PrizeOrder
 */
public interface PrizeOrderMapper extends BaseMapper<PrizeOrder> {


    /**
     * 抽奖金额统计
     */
    @Select("SELECT IFNULL(sum(price),0) AS priceStaticstics FROM li_prize_order WHERE pay_status = 2 and create_time >=#{startTime}  AND create_time <= #{endTime}")
    PrizeOrderStatisticsVO prizePriceStatistics(PrizeOrderDTO dto);


    /**
     * 抽奖用户数量统计
     * @param dto
     * @return
     */
    @Select("SELECT count(subQuery.user_id) AS userCountStaticstics FROM( " +
            "SELECT user_id FROM li_prize_order WHERE pay_status = 2 and create_time >=#{startTime}  AND create_time <= #{endTime} GROUP BY user_id) AS subQuery")
    PrizeOrderStatisticsVO prizeUserCountStatistics(PrizeOrderDTO dto);

    @Select("SELECT\n" +
            "\tIFNULL( sum( CAST( REGEXP_REPLACE ( prize_name, '[^0-9]+', '' ) AS UNSIGNED )), 0 ) AS fScore \n" +
            "FROM\n" +
            "\tlilishop_order.li_prize_order lpo\n" +
            "\tLEFT JOIN lilishop_order.li_prize_goods lpg ON lpo.prize_id = lpg.id \n" +
            "WHERE\n" +
            "\tlpo.pay_status = 2 \n" +
            "\tAND lpo.create_time >= #{startTime}  \n" +
            "\tAND lpo.create_time <= #{endTime} \n" +
            "\tAND lpg.virtual_type >= 30 \n" +
            "\tAND lpg.virtual_type <= 39")
    PrizeOrderStatisticsVO prizeFScoreStatistics(PrizeOrderDTO dto);

    @Select("SELECT\n" +
            "\tIFNULL( sum( CAST( REGEXP_REPLACE ( prize_name, '[^0-9]+', '' ) AS UNSIGNED )), 0 ) AS wingedBean \n" +
            "FROM\n" +
            "\tlilishop_order.li_prize_order lpo\n" +
            "\tLEFT JOIN lilishop_order.li_prize_goods lpg ON lpo.prize_id = lpg.id \n" +
            "WHERE\n" +
            "\tlpo.pay_status = 2 \n" +
            "\tAND lpo.create_time >= #{startTime}  \n" +
            "\tAND lpo.create_time <= #{endTime} \n" +
            "\tAND lpg.virtual_type >= 40 \n" +
            "\tAND lpg.virtual_type <= 49")
    PrizeOrderStatisticsVO prizeWingedBeanStatistics(PrizeOrderDTO dto);
}